<?php
/**
 * 用户处理邮件类（退订，打开等等）
 * Created by PhpStorm.
 * User: Liujianlin
 * Date: 2017/6/27
 * Time: 11:03
 */

namespace app\controllers\marketing;

use yii;
use app\models\marketing\MailGroupEmailTypeStatistics;
use app\components\Site;
use app\models\marketing\MailSendlist;
use app\models\marketing\MailOpenTemp;
use app\models\marketing\EmailOpenRecord;
use app\models\marketing\User;
use app\models\marketing\MailGroup;
use app\models\Elog;
use app\components\EmailMarket;
use app\components\redis\RedisLock;
use app\components\redis\RedisMarketingEmail;

use yii\mongodb\Query;
use yii\mongodb\ActiveRecord;


class MailUserDealController extends \app\base\Controller
{
    public $enableCsrfValidation = false;       // 关闭csrf验证

    protected $dirPath = ROOT_PATH . '/bounces_data/open_data/';

    /**
     * 记录打开邮件到mongodb
     * @return string
     */
    public function actionOpen()
    {
        $method = yii::$app->request->getMethod();

        // 获取并过滤sendlist_id
        $sendListId = yii::$app->request->$method("m");
        $sendListId = intval($sendListId);
        $sendListId = !empty($sendListId) ? $sendListId : 0;

        // 获取并过滤group_id
        $groupId = yii::$app->request->$method("g");
        $groupId = intval($groupId);
        $groupId = !empty($groupId) ? $groupId : 0;

        // 获取并过滤site_id
        $siteId = yii::$app->request->$method("s");
        $siteId = intval($siteId);
        $siteId = !empty($siteId) ? $siteId : 0;

        // 获取并过滤user_id
        $userId = yii::$app->request->$method("u");
        $userId = intval($userId);
        $userId = !empty($userId) ? $userId : 0;
        
        // 获取并过滤site_user_id
        $siteUserId = yii::$app->request->$method("u1");
        $siteUserId = !empty($siteUserId) ? intval($siteUserId) : 0;
        
        // 获取并过滤email_type
        $emailType = yii::$app->request->$method("t");
        $emailType = !empty($emailType) ? intval($emailType) : 0;
        
        
        // 获取并过滤token
        $token = yii::$app->request->$method("token");
        $token = trim($token);
        $token = !empty($token) ? $token : '';

        $open_time = yii::$app->request->$method('open_time');
        $open_teminal = yii::$app->request->$method('open_teminal');

        if($open_teminal  === null){
            // 获取打开设备信息
            $agent = yii::$app->request->getUserAgent();
//        var_dump($agent);
            // 设备类型 0：other 1：ios 2：android 3：pc
            if (stripos($agent, "iPhone") !== false || stripos($agent, "iPad") !== false) {
                $open_teminal = 1;
            } else if (stripos($agent, "Android") !== false) {
                $open_teminal = 2;
            } else if (stripos($agent, "Windows") !== false || stripos($agent, "mac") !== false) {
                $open_teminal = 3;
            } else {
                $open_teminal = 0;
            }
        }

        $queryString = yii::$app->request->getQueryString() . '&open_teminal=' . $open_teminal . '&open_time=' . ($open_time?:time());

        try {
            if (!$siteId || !$groupId || !$sendListId) {
                Elog::logCrontab("参数不存在! params: siteId:" . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId . ';emailType:'.$emailType.';token:' . $token);
                echo '参数不存在!';
                exit();
            }
            $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);
            // 分组id,发送队列id小于等于0,站点名称不存在退出
            if ($groupId <= 0 || $sendListId <= 0 || !$siteShortName) {
                Elog::logCrontab('groupId:' . $groupId . ';sendListId:' . $sendListId . ';小于等于0,或siteId：' . $siteId . ';不存在! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId .';emailType:'.$emailType. ';token:' . $token);
                echo 'groupId:' . $groupId . ';sendListId:' . $sendListId . ';小于等于0,或siteId：' . $siteId . ';不存在!';
                exit();
            }

            // 进行参数验证,兼容以前的数据
            if ($userId || $token) {
                $emailTakon = yii::$app->params['email_check_code'];
                $emailCode = base64_decode(urldecode($token));
                
                if(empty($emailCode)) {
                    Elog::logCrontab('The parameter token does not exist! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId . ';emailType:'.$emailType.';token:' . $token);
                    echo 'The parameter token does not exist!';
                    exit();
                }
                
                if($emailType) {
                    if (md5($groupId . '-' . $siteId . '-' . $sendListId . '-' . $userId .'-'.$siteUserId. '-' .$emailType.'-'. $emailTakon) != $emailCode) {
                        Elog::logCrontab('The verification is not passed! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId . ';emailType:'.$emailType.';token:' . $token);
                        echo 'The verification is not passed!';
                        exit();
                    }
                } else {
                    if (md5($groupId . '-' . $siteId . '-' . $sendListId . '-' . $userId . '-' . $emailTakon) != $emailCode) {
                        Elog::logCrontab('The verification is not passed! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId . ';emailType:'.$emailType.';token:' . $token);
                        echo 'The verification is not passed!';
                        exit();
                    }
                }
            }

            // 将user_id还原
            if ($userId) {
                $userId = substr($userId, 2);
            }
            
            // 将site_user_id还原
            if ($siteUserId) {
                $siteUserId = substr($siteUserId, 2);
            }
            
            // 如果不存在user_id或不存在email_type,从队列表中查询获取
            if (!$userId || !$emailType) {
                $sendListModel = new MailSendlist();
                $sendlistOne = $sendListModel->getSendListOne($siteId,$groupId, $sendListId);

                // 如果记录不存在或者数据库中的group_id 和当前的分组id不一致直接退出
                if (!$sendlistOne || $sendlistOne['group_id'] != $groupId) {
                    Elog::logCrontab('The queue data does not exist or the queue data group id is inconsistent with the group id! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId . ';emailType:'.$emailType.';token:' . $token);
                    echo 'The queue data does not exist or the queue data group id is inconsistent with the group id!';
                    exit();
                }
                $userId = $sendlistOne['user_id'];
                $emailType = $sendlistOne['email_type'];
                $siteUserId = $sendlistOne['site_user_id'];
            }

//            // 组装写入MongoDB的数据,更新数据的条件
//            $updateCondition = [
//                'group_id' => $groupId,
//                'sendlist_id' => $sendListId
//            ];
//
//            // 需要更新的数据
//            $updateData = [
//                '$inc' => [
//                    'open_count' => 1
//                ],
//                '$setOnInsert' => [
//                    'user_id' => $userId,
//                    'open_teminal' => $open_teminal,
//                    'open_time' => $open_time ?: time(),
//                    'is_sync' => 0
//                ]
//            ];
//
//            // 如果存在group_id和sendlist_id的记录就更新,否则就插入
//            Yii::$app->mongodb->getCollection('open_report_record_' . $siteShortName)->update($updateCondition, $updateData, array('upsert' => true));
            
            // 将打开数据写入文件中，定时把文件中的打开数据导入到数据表中
            //如果出现异常，则将打开数据保存到文件里面，避免打开数据丢失
            if (!is_dir($this->dirPath)) {
                echo $this->dirPath . ' 目录不存在请手动创建';
                exit;
            }
            $filename = $this->dirPath . '/open_data_' . $siteId;
            $siteUserId = $siteUserId ? intval($siteUserId) : 0;
            $writeFileData = "(".$groupId.",".$sendListId.",".$userId.",".$siteUserId.",".$open_teminal.",".$emailType.",".($open_time ?: time()).",1)";
            
            file_put_contents($filename, $writeFileData . ",", FILE_APPEND);
            
            echo '数据保存成功！';
        } catch (\Exception $e) {
            echo $e->getMessage();
            Elog::logCrontab($e->getMessage() . '；QueryString：' . $queryString, 'MarketingMailOpen', 'exception');
            //如果出现异常，则将打开数据保存到文件里面，避免打开数据丢失
            if (!is_dir($this->dirPath)) {
                echo $this->dirPath . ' 目录不存在请手动创建';
                exit;
            }
            $filename = $this->dirPath . '/site_' . $siteId;
            file_put_contents($filename, $queryString . "\n", FILE_APPEND);
        }
    }
    
    /**
     * 定时把报错在文件中的打开数据写入数据表
     */
    public function actionDealOpenDataFromFileToMysql()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $method = yii::$app->request->getMethod();

        // 获取网站id
        $siteId = yii::$app->request->$method("site_id");
        $siteId = $siteId ? intval($siteId) : 0;
        
        
        $siteInfo = yii::$app->params['sites']['marketing_email']['web_index'];
        
        $siteName = isset($siteInfo[$siteId]) ? $siteInfo[$siteId] : '';
        if(!$siteName) {
            echo "站点不存在！";
            exit();
        }
        
        //判断打开数据文件是否存在
        $openFile = $this->dirPath . 'open_data_' . $siteId;
        $newOpenFile = $this->dirPath . 'tmp_open_data_' . $siteId;
        $saveFailOpenFile = $this->dirPath . 'save_fail_open_data_' . $siteId;
            
        //数据文件存在且更改文件名
        if(file_exists($openFile) && @rename($openFile,$newOpenFile)) {

            // 数据写入数据库成功后，把数据移到别的文件保存，并删除该文件
            
            $getOpenData = file_get_contents($newOpenFile);
            $getOpenData = trim($getOpenData);
            $openData = rtrim($getOpenData,",");
            
            if($openData) {
                try{
                    $insertSql = "insert into email_{$siteName}_open_data"
                        . "(group_id,sendlist_id,user_id,site_user_id,open_teminal,email_type,open_time,open_count) values".$openData
                        . ' ON DUPLICATE KEY UPDATE open_count=open_count+1';
                    Yii::$app->db->createCommand($insertSql)->execute();
                } catch(\Exception $e) {
                    // 数据写入数据库失败时把数据写入到另一个文件中，并进行告警
                    file_put_contents($saveFailOpenFile, $getOpenData,FILE_APPEND);
                    
                    $msgCenter = new \app\components\msgcenter\MsgCenter();
                    
                    $dealData['site_id'] = 99; // 发送人员为EMP所属人员
                    $dealData['title'] = "EMP营销邮件，{$siteName}站解析文件中的打开数据到数据表时失败！";



                    $dealData['exceptionEmailTemplateContent'] = "EMP营销邮件，{$siteName}站解析文件中的打开数据到数据表时失败！errorMsg:".$e->getMessage();

                    $dealData['exceptionWechatTemplateContent'] = "EMP营销邮件，{$siteName}站解析文件中的打开数据到数据表时失败！errorMsg:".$e->getMessage();

                    $msgCenter->go_msgcenter($dealData); 
                    
                    echo "EMP营销邮件，{$siteName}站解析文件中的打开数据到数据表时失败！errorMsg:".$e->getMessage();
                }
            }
            
            // 删除$newOpenFile
            unlink($newOpenFile);
        }
        
        
        return '数据处理成功！';
    }
    
    
    
    public function actionSplitFile()
    {

        $method = yii::$app->request->getMethod();
        // 获取并过滤sendlist_id
        $filename = yii::$app->request->$method("filename");
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 1000;
        $loadFilePath = yii::$app->getRuntimePath() . '/elogOpenData/';
        if (!is_readable($loadFilePath . $filename)) {
            return '文件不存在！';
        }
        $handle = fopen($loadFilePath . $filename, 'r');
        if ($handle) {
            $i = 0;
            $j = 0;
            $splitArr = [];
            while (($buffer = fgets($handle)) !== false && !feof($handle)) {

                $reg = "/siteId:(\d+);groupId:(\d+);sendListId:(\d+);userId:(\d+);/i";
                $res = preg_match($reg, $buffer, $matchs);
                if ($res) {
                    $siteId = intval($matchs[1]);
                    $groupId = intval($matchs[2]);
                    $sendListId = intval($matchs[3]);
                    $userId = intval($matchs[4]);

                    $splitArr[] = [
                        'siteId' => $siteId,
                        'groupId' => $groupId,
                        'sendlistId' => $sendListId,
                        'userId' => $userId
                    ];


                }
                $i++;

                if ($i >= $limit) {

                    $splitFileName = yii::$app->getRuntimePath() . '/elogOpenData/php-' . $j . '.txt';
                    file_put_contents($splitFileName, var_export($splitArr, true));

                    $splitArr = [];
                    $i = 0;
                    $j++;
                }


            }

            if ($splitArr) {
                $splitFileName = yii::$app->getRuntimePath() . '/elogOpenData/php-' . $j . '.txt';
                file_put_contents($splitFileName, var_export($splitArr, true));
            }


        }

        fclose($handle);
        return $filename . '文件拆分完成！';


    }

    /*
     *  手动将拆分的文件数据刷新到mongodb
     */
    public function actionManualAnalysisSplitFileToMongodb()
    {
//        ignore_user_abort(true); 
//        set_time_limit(0);
        $method = yii::$app->request->getMethod();
        $fileId = yii::$app->request->$method("fileId") ? yii::$app->request->$method("fileId") : 0;

        $loadFilePath = yii::$app->getRuntimePath() . '/elogOpenData/';

        $handleFileName = $loadFilePath . 'php-' . $fileId . '.txt';

        if (!file_exists($handleFileName)) {
            return '文件不存在！';
        }
        echo $handleFileName;
        $handle = fopen($handleFileName, 'r');
        if ($handle) {

            $contents = fread($handle, filesize($handleFileName));
            eval("\$contents=$contents;");
//            var_dump($contents);exit;
            foreach ($contents as $content) {
                $siteShortName = Site::getInstance()->getSiteShortNameById($content['siteId']);
                if ($siteShortName) {
                    // 组装写入MongoDB的数据,更新数据的条件
                    $updateCondition = [
                        'group_id' => $content['groupId'],
                        'sendlist_id' => $content['sendlistId']
                    ];

                    // 需要更新的数据
                    $updateData = [
                        '$inc' => [
                            'open_count' => 1
                        ],
                        '$setOnInsert' => [
                            'user_id' => $content['userId'],
                            'open_time' => time(),
                            'is_sync' => 0
                        ]
                    ];

                    // 如果存在group_id和sendlist_id的记录就更新,否则就插入
                    try {
                        Yii::$app->mongodb->getCollection('open_report_record_' . $siteShortName)->update($updateCondition, $updateData, array('upsert' => true));
                    } catch (\Exception $e) {
                        $msg = $e->getMessage() . 'AnalysisElogToMongodb! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId;
                        Elog::logCrontab($msg, 'actionOpen', 'exception');
                        echo $e->getMessage() . 'AnalysisElogToMongodb! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId;
                        Yii::$app->mongodb->close();
                        exit;
                    }


                }
            }

        } else {
            return '打开文件失败！';
        }
        fclose($handle);
        Yii::$app->mongodb->close();
        // 处理完成后删除该文件
        unlink($handleFileName);

        return;
    }

    /*
     *  手动将elog中未写入mongodb的打开数据解析到mongodb
     */
    public function actionManualAnalysisElogToMongodb()
    {
//        ignore_user_abort(true); 
//        set_time_limit(0);
        $method = yii::$app->request->getMethod();
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 200;
        // 获取并过滤sendlist_id
        $filename = yii::$app->request->$method("filename");
        $curPos = yii::$app->request->$method("curPos") ? yii::$app->request->$method("curPos") : 0;
//        require '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';
//        $objectPHPExcel = new \PHPExcel();
//        require_once('../vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel2007.php');
        $loadFilePath = yii::$app->getRuntimePath() . '/elogOpenData/';
//        $objReader = new \PHPExcel_Reader_Excel2007;  
//        $phpExcelObj = $objReader->load($loadFilePath.'exception_open_fail.xls');

//        $rows = $phpExcelObj->getSheet(1);
        if (!file_exists($loadFilePath . $filename)) {
            return '文件不存在！';
        }
        echo $loadFilePath . $filename;
        $handle = fopen($loadFilePath . $filename, 'r');
        fseek($handle, $curPos);
        if ($handle) {
            $i = 0;
            while (($buffer = fgets($handle)) !== false && !feof($handle)) {

                $reg = "/siteId:(\d+);groupId:(\d+);sendListId:(\d+);userId:(\d+);/i";
                $res = preg_match($reg, $buffer, $matchs);
                if ($res) {
                    $siteId = intval($matchs[1]);
                    $groupId = intval($matchs[2]);
                    $sendListId = intval($matchs[3]);
                    $userId = intval($matchs[4]);

                    $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);
                    if ($siteShortName) {
                        // 组装写入MongoDB的数据,更新数据的条件
                        $updateCondition = [
                            'group_id' => $groupId,
                            'sendlist_id' => $sendListId
                        ];

                        // 需要更新的数据
                        $updateData = [
                            '$inc' => [
                                'open_count' => 1
                            ],
                            '$setOnInsert' => [
                                'user_id' => $userId,
                                'open_time' => time(),
                                'is_sync' => 0
                            ]
                        ];

                        // 如果存在group_id和sendlist_id的记录就更新,否则就插入
                        try {
                            Yii::$app->mongodb->getCollection('open_report_record_' . $siteShortName)->update($updateCondition, $updateData, array('upsert' => true));
                        } catch (\Exception $e) {
                            $msg = $e->getMessage() . 'AnalysisElogToMongodb! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId;
                            Elog::logCrontab($msg, 'actionOpen', 'exception');
                            echo $e->getMessage() . 'AnalysisElogToMongodb! params: siteId:' . $siteId . ';groupId:' . $groupId . ';sendListId:' . $sendListId . ';userId:' . $userId;
                            Yii::$app->mongodb->close();
                            exit;
                        }


                    }
                }
                $i++;
                if ($i > $limit) {
                    $curPos = ftell($handle);
                    fclose($handle);
                    Yii::$app->mongodb->close();
                    sleep(10);
                    // 处理完成后
                    $host = YII_ENV_PROD ? 'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/';
                    echo '当前批次处理完成！';
                    echo "<script type='text/javascript'>location.href='" . $host . 'marketing/mail-user-deal/manual-analysis-elog-to-mongodb?site=marketing_email&filename=' . $filename . '&curPos=' . $curPos . '&limit=' . $limit . "';</script>";
                    exit;
                }
            }
        } else {
            return '打开文件失败！';
        }
        fclose($handle);
        return;
    }

    /*
     * 手动刷新MySQL打开临时表中未刷新的数据到mongodb中,并标识为未同步
     * @return string
     */
    public function actionManualSyncMysqlOpenTempToMongodb()
    {
        $limit = 2000; //每次处理的数据范围
        // 获取打开明细数据
        $getOpenRecordSql = "select * from mail_open_temp order by id asc limit $limit";
        $openResult = Yii::$app->db->createCommand($getOpenRecordSql)->queryAll();
        if ($openResult) {
            // 初始化变量
            $sendlistIdArr = $openTempArr = $openTempIds = [];
            foreach ($openResult as $open) {
                $openTempIds[] = $open['id'];
                $sendlistIdArr[$open['site_id']][] = $open['sendlist_id'];
                $openTempArr[$open['site_id']][] = $open;
            }

            foreach ($openTempArr as $siteId => $openTemp) {

                $updateData = $sendlistArr = $updateCondition = [];

                $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);

                if (!$siteShortName) {
                    continue;
                }
                if (isset($sendlistIdArr[$siteId])) {
                    $getSendlistSql = "select user_id,id from mail_sendlist_{$siteShortName} where id in(" . join(',', $sendlistIdArr[$siteId]) . ")";
                    $sendlistResult = Yii::$app->db->createCommand($getSendlistSql)->queryAll();
                }
                foreach ($sendlistResult as $sendlist) {
                    $sendlistArr[$sendlist['id']] = $sendlist['user_id'];
                }

                // 组装数据
                foreach ($openTemp as $open) {

                    $updateCondition = [
                        'group_id' => $open['group_id'],
                        'sendlist_id' => $open['sendlist_id']
                    ];

                    // 需要更新的数据
                    $updateData = [
                        '$inc' => [
                            'open_count' => 1
                        ],
                        '$setOnInsert' => [
                            'user_id' => isset($sendlistArr[$open['sendlist_id']]) ? $sendlistArr[$open['sendlist_id']] : 0,
                            'open_time' => $open['open_time'],
                            'is_sync' => 0
                        ]
                    ];

                    // 如果存在group_id和sendlist_id的记录就更新,否则就插入
                    try {
                        Yii::$app->mongodb->getCollection('open_report_record_' . $siteShortName)->update($updateCondition, $updateData, array('upsert' => true));
                    } catch (\Exception $e) {
                        echo $e->getMessage() . '! params: RecordId:' . $open['id'];
                    }
                }
            }
            
            // 删除已经处理完成的数据
            if ($openTempIds) {
                Yii::$app->db->createCommand("delete from mail_open_temp where id in (" . join(',', $openTempIds) . ")")->execute();
            }

            // 处理完成后
            $host = YII_ENV_PROD ? 'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/';
            echo '当前批次处理完成！';
            echo "<script type='text/javascript'>location.href='" . $host . 'marketing/mail-user-deal/manual-sync-mysql-open-temp-to-mongodb?site=marketing_email' . "';</script>";
            exit;
        }
        echo 'mail_open_temp数据同步完成！';
    }

    /*
     * 手动刷新MySQL打开明细表的数据到mongodb中
     * @return string
     */
    public function actionManualSyncMysqlToMongodb()
    {
        $method = yii::$app->request->getMethod();

        $siteId = yii::$app->request->$method("site_id");
        $start = yii::$app->request->$method("start") ? yii::$app->request->$method("start") : 0;
        $max = yii::$app->request->$method("max") ? yii::$app->request->$method("max") : 0;

        $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);

        if (!$siteShortName) {
            return '站点不存在！';
        }

        $limit = 2000; //每次处理的数据范围
        $stop = $start + $limit;
        // 获取打开明细数据
        $getOpenRecordSql = "select * from user_{$siteShortName}_email_open_record where id between $start and $stop";
        $openResult = Yii::$app->db->createCommand($getOpenRecordSql)->queryAll();
        if ($openResult) {
            // 初始化变量
            $sendlistIdArr = $updateData = $sendlistArr = $updateCondition = [];
            foreach ($openResult as $open) {
                if ($open['sendlist_id']) {
                    $sendlistIdArr[] = $open['sendlist_id'];
                }
            }
            if ($sendlistIdArr) {
                $getSendlistSql = "select group_id,id from mail_sendlist_{$siteShortName} where id in(" . join(',', $sendlistIdArr) . ")";
                $sendlistResult = Yii::$app->db->createCommand($getSendlistSql)->queryAll();
            }

            foreach ($sendlistResult as $sendlist) {
                $sendlistArr[$sendlist['id']] = $sendlist['group_id'];
            }

            // 组装数据
            foreach ($openResult as $open) {

                $updateCondition = [
                    'group_id' => isset($sendlistArr[$open['sendlist_id']]) ? $sendlistArr[$open['sendlist_id']] : 0,
                    'sendlist_id' => $open['sendlist_id']
                ];

                // 需要更新的数据
                $updateData = [
                    '$inc' => [
                        'open_count' => 1
                    ],
                    '$setOnInsert' => [
                        'user_id' => $open['user_id'],
                        'open_time' => $open['email_open_time'],
                        'is_sync' => 1
                    ]
                ];

                // 如果存在group_id和sendlist_id的记录就更新,否则就插入
                try {
                    Yii::$app->mongodb->getCollection('open_report_record_' . $siteShortName)->update($updateCondition, $updateData, array('upsert' => true));
                } catch (\Exception $e) {
                    echo $e->getMessage() . '! params: RecordId:' . $open['id'];
                }

            }

            // 处理完成后
            $host = YII_ENV_PROD ? 'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/';
            echo '当前批次处理完成！';
            echo "<script type='text/javascript'>location.href='" . $host . 'marketing/mail-user-deal/manual-sync-mysql-to-mongodb?site=marketing_email&start=' . ($stop + 1) . '&site_id=' . $siteId . '&max=' . $max . "';</script>";
            exit;
        } else if ($start < $max) {
            // 处理完成后
            $host = YII_ENV_PROD ? 'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/';
            echo '当前批次处理完成！';
            echo "<script type='text/javascript'>location.href='" . $host . 'marketing/mail-user-deal/manual-sync-mysql-to-mongodb?site=marketing_email&start=' . ($stop + 1) . '&site_id=' . $siteId . '&max=' . $max . "';</script>";
            exit;
        }
        echo '站点' . $siteShortName . "数据同步完成！";
    }

    /**
     * 显示用户购物车邮件退订页面
     */
    public function actionUnsubscribeCart()
    {
        $hostName = $_SERVER['HTTP_HOST'];
//        $hostName = 'ems.rosewholesale.com';

        $siteName = preg_replace('/^\w*\./', '', $hostName);        // 去掉前缀。如ems.gearbest.com去掉前缀就是gearbest.com

        $siteId = Site::getInstance()->getSiteId($siteName);
        if (empty($siteId)) {
            echo "站点不存在，请检查再试";
            exit();
        }
        $method = yii::$app->request->getMethod();
        $data['email'] = yii::$app->request->$method("email");
        $data['type_id'] = yii::$app->request->$method("type_id");
        $data['site_name'] = $siteName;
        $data['sender_name'] = Site::getInstance()->getSenderName($siteId);
        $data['site'] = explode(".", $data['sender_name'])[0];
        $data['site_home'] = Site::getInstance()->getSiteHome($siteId);
        $data['logo'] = Site::getInstance()->getLogo($siteId);
        if (strlen($data['site']) > 9) {
            $data['table_width'] = "70%";
        } else {
            $data['table_width'] = "60%";
        }

        return $this->renderPartial("/marketing/mail-user-deal/unsubscribe-cart", ["data" => $data]);
    }

    /**
     * 保存用户退订
     */
    public function actionDoUnsubscribeCart()
    {
        $emailObj = new EmailMarket();
        $hostName = $_SERVER['HTTP_HOST'];

        $siteName = preg_replace('/^\w*\./', '', $hostName);        // 去掉前缀。如ems.gearbest.com去掉前缀就是gearbest.com

        $siteId = Site::getInstance()->getSiteId($siteName);
        if (empty($siteId)) {
            $this->returnJson(10008, "站点不存在, site_name[{$siteName}]");
        }

        $userModel = new User();
        $method = yii::$app->request->getMethod();
        $email = yii::$app->request->$method("email");
        $type_id = yii::$app->request->$method("type_id");
        $type_id = intval($type_id);
        $encryptEmail = $emailObj->encryption($email);
        if (!$type_id) {
            $this->returnJson(10008, "邮件类型不存在, site_name[{$siteName}]");
        }
        $userModel->updateSubscribeCartInfo($siteId, $encryptEmail, $type_id);

        $this->returnJson(0, "success");
    }

    /**
     * 显示用户退订页面
     */
    public function actionUnsubscribe()
    {
        $hostName = $_SERVER['HTTP_HOST'];
//        $hostName = 'ems.zaful.com';
        $siteName = preg_replace('/^\w*\./', '', $hostName);        // 去掉前缀。如ems.gearbest.com去掉前缀就是gearbest.com
        $siteId = Site::getInstance()->getSiteId($siteName);
        if (empty($siteId)) {
            echo "站点不存在，请检查再试";
            exit();
        }
        $method = yii::$app->request->getMethod();
        $data['email'] = yii::$app->request->$method("email");
        $data['code'] = yii::$app->request->$method("code");
        $data['utm_campaign'] = yii::$app->request->$method("utm_campaign");

        $emailMarket = new EmailMarket();
        if (empty($data['email']) || !$emailMarket->isEmail($data['email'])) {
            echo "邮箱不存在或格式不正确！";
            exit();
        }
        if ($data['code']) {

            $email_takon = yii::$app->params['email_check_code'];
            $email_code = base64_decode(urldecode($data['code']));
            if (empty($email_code)) {
                echo "页面无法访问！";
                exit();
            }

            if ($data['utm_campaign']) {

                if (md5($email_takon . $data['email'] . $data['utm_campaign']) != $email_code) {
                    echo "页面无法访问！";
                    exit();
                }

            } else {

                if (md5($email_takon . $data['email']) != $email_code) {
                    echo "页面无法访问！";
                    exit();
                }

            }

        }

        $data['site_name'] = $siteName;
        $data['sender_name'] = Site::getInstance()->getSenderName($siteId);
        $data['logo'] = Site::getInstance()->getLogo($siteId);
        $data['site_home'] = Site::getInstance()->getSiteHome($siteId);
        return $this->renderPartial("/marketing/mail-user-deal/unsubscribe", ["data" => $data]);
    }

    /**
     * 保存用户退订
     */
    public function actionDoUnsubscribe()
    {

        $hostName = $_SERVER['HTTP_HOST'];;
//        $hostName = 'ems.zaful.com';
        $siteName = preg_replace('/^\w*\./', '', $hostName);        // 去掉前缀。如ems.gearbest.com去掉前缀就是gearbest.com
        $siteId = Site::getInstance()->getSiteId($siteName);

        if (empty($siteId)) {
            $this->returnJson(10008, "站点不存在, site_name[{$siteName}]");
        }


        $method = yii::$app->request->getMethod();
        $email = yii::$app->request->$method("email");
        $frequency = yii::$app->request->$method("frequency");
        $campaign = yii::$app->request->$method("utm_campaign");
        $code = yii::$app->request->$method("code");
        
        $emailMarket = new EmailMarket();
        if (empty($email) || !$emailMarket->isEmail($email)) {
            $this->returnJson(10008, "邮箱不存在或格式不正确！");
        }
        
        if(!isset($code) || empty($code)) {
            $this->returnJson(10008, "参数不正确！");
        }
        if ($code) {

            $email_takon = yii::$app->params['email_check_code'];
            $email_code = base64_decode(urldecode($code));
            if (empty($email_code)) {
                echo "页面无法访问！";
                exit();
            }


            if ($campaign) {

                if (md5($email_takon . $email . $campaign) != $email_code) {
                    echo "页面无法访问！";
                    exit();
                }

            } else {

                if (md5($email_takon . $email) != $email_code) {
                    echo "页面无法访问！";
                    exit();
                }

            }

        }
        
        
        // 获取网站名称
        $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);
        $groupId = 0;
        if ($campaign) {
            $groupModel = new MailGroup();
            $groupId = $groupModel->getGroupIdByGroupName($campaign);

        }
//        var_dump($groupId);exit;
        $emailObj = new EmailMarket();
        $encryptEmail = $emailObj->encryption($email);
        // 组装写入MongoDB的数据,更新数据的条件
        $updateCondition = [
            'group_id' => $groupId,
            'email' => $encryptEmail
        ];

        // 需要更新的数据
        $updateData = [
            '$set' => [
                'unsubscribe_time' => time(),
                'is_sync' => 0,
                'frequency' => $frequency
            ],
            '$setOnInsert' => [
                'unsubscribe_source' => 4, // 退订来源：EDM邮件
            ]
        ];

        // 如果存在group_id和email的记录就更新,否则就插入
        try {
            Yii::$app->mongodb->getCollection('mail_group_unsubscribe_' . $siteShortName)->update($updateCondition, $updateData, array('upsert' => true));
        } catch (\Exception $e) {
            $msg = $e->getMessage() . '! params: siteId:' . $siteId . ';groupId:' . $groupId . ';email:' . $email . ';unsubscribe_time:' . time() . ';frequency:' . $frequency;
            Elog::logCrontab($msg, 'actionDoUnsubscribe', 'exception');

        }

        $this->returnJson(0, "success");
    }

    /*
     * 每天定时将mongodb中用户退订数据更新到mysql用户表中
     * @return string
     * $author liugang 2018-2-24
     */
    public function actionSynUnsubscribeDataToUserTable()
    {
        ignore_user_abort(true);
        set_time_limit(0);

        $method = yii::$app->request->getMethod();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 1000; // 每次处理的数据量

        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            return 'site information does not exist! params: siteId:' . $siteId;
        }

        $query = new Query;
        // 从mongodb中获取要更新的数据
        $unsubsArr = $query->from('mail_group_unsubscribe_' . $siteName)->where(['is_sync' => 0])->limit($limit)->all();

        // 变量初始化
        $mongoIds = [];
        $userModel = new User();
        if ($unsubsArr) {
            foreach ($unsubsArr as $unsub) {
                $mongoIds[] = $unsub['_id'];
                if ($unsub['unsubscribe_source'] == 4) // 退订来源EDM邮件
                {
                    $subscribeState = 1;
                    if ($unsub['frequency'] == 0) {
                        $subscribeState = 2;
                        $unsub['frequency'] = 1;
                    }
                    $userModel->updateSubscribeInfo($siteId, $unsub['email'], $subscribeState, $unsub['frequency'], $unsub['unsubscribe_source'], $unsub['unsubscribe_time']);

                } else {
                    $userModel->unSub($siteId, $unsub['email'], $unsub['unsubscribe_source'], $unsub['unsubscribe_time']);
                }
            }

            // 更改处理过的数据状态
            Yii::$app->mongodb->getCollection('mail_group_unsubscribe_' . $siteName)->update(array('_id' => $mongoIds), array('is_sync' => 1));
        }
        return '修改退订用户数据完成！';
    }
    
    /*
     * 每天定时将用户邮件退订数据按指定温度统计到channel_return_info_statistics表中
     * /marketing/mail-user-deal/syn-unsubscribe-data-to-channel-table?site=marketing_email&site_id=12
     */
    public function actionSynUnsubscribeDataToChannelTable()
    {
        ignore_user_abort(true);
        set_time_limit(0);

        $method = yii::$app->request->getMethod();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            return 'site information does not exist! params: siteId:' . $siteId;
        }
        
        // 从mongodb中获取要更新的数据
        $unsubsArr = Yii::$app->mongodb->getCollection('mail_group_unsubscribe_' . $siteName)->aggregate([['$match' => ['unsubscribe_source' => 4]], ['$group' => ['_id' => '$group_id', 'total' => ['$sum' => 1]]]]);
//        var_dump($unsubsArr);exit;
        $updateSql = 'update mail_group_conversion_statistics set email_unsubscribe_nums = CASE group_id ';
        $updateArr = [];
        
        foreach ($unsubsArr as $unsubs) {

            $updateArr[] = sprintf("WHEN %d THEN %d ", $unsubs['_id'], $unsubs['total']);
        }
        if ($updateArr) {
            $updateSql .= join('', $updateArr)." END";
            Yii::$app->db->createCommand($updateSql)->execute();
        }
        return '数据处理完成！';
        
    }
    /**
     * 保存用户退订
     */
    public function actionDoUnsubscribe1()
    {
        $emailObj = new EmailMarket();
        $hostName = $_SERVER['HTTP_HOST'];;
        $siteName = preg_replace('/^\w*\./', '', $hostName);        // 去掉前缀。如ems.gearbest.com去掉前缀就是gearbest.com
        $siteId = Site::getInstance()->getSiteId($siteName);

        if (empty($siteId)) {
            $this->returnJson(10008, "站点不存在, site_name[{$siteName}]");
        }

        $userModel = new User();
        $method = yii::$app->request->getMethod();
        $email = yii::$app->request->$method("email");
        $frequency = yii::$app->request->$method("frequency");
        $campaign = yii::$app->request->$method("utm_campaign");

        $subscribeState = 1;
        $source = 4;       // EDM邮件
        $encryptEmail = $emailObj->encryption($email);
        if (0 == $frequency) {   // 退订。修改退订状态和频率
            $subscribeState = 2;
            $frequency = 1;
        }

        $userModel->updateSubscribeInfo($siteId, $encryptEmail, $subscribeState, $frequency, $source);

        $this->returnJson(0, "success");
    }

    /**
     * 同步mongodb的打开记录到真实表中,分网站定时每15分钟执行一次
     * @return string
     */
    public function actionSynOpen()
    {
        ignore_user_abort(true);
        set_time_limit(0);
        $method = yii::$app->request->getMethod();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 1000; // 每次处理的数据量
        $byGroupId = yii::$app->request->$method("byGroupId") ? yii::$app->request->$method("byGroupId") : 0; // 是否按照分组来发

        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            $msg = 'site information does not exist! params: siteId:' . $siteId;
            Elog::logCrontab($msg, 'actionSynOpen', 'info');
            return 'site information does not exist! params: siteId:' . $siteId;
        }

        // 对当前请求加锁
//        $redis = new RedisMarketingEmail();
//        
//        $redisLock = new RedisLock($redis, "actionUserOpen-".$siteName, 300);
//            
//        if ($redisLock->translock() === false) {
//            $msg = 'There is a request to reject the request in the process.';
//            Elog::logCrontab($msg, 'actionSynOpen','info');
//            return '有请求在处理中，拒绝该请求！';
//           
//        }

        $sendListModel = new MailSendlist();
        $emailOpenRecordModel = new EmailOpenRecord();
        $userModel = new User();

        try {
            // 从mongo中获取当前网站的未处理的数据，每次取1000条
//                $opensArr = $query->from('open_report_record_'.$siteName)->where(['is_sync'=>0])->limit($limit)->all();
            while ($opensArr = $this->getOpenRecordFromMongo($siteName, $byGroupId, $limit)) {
                // 变量初始化
                $redisValues = $siteSendlistOpentime = $emailOpentime = $insertOpenRecordData = $groupTemplateid = [];
                foreach ($opensArr as $open) {

                    $redisValues[] = $open['_id'];

                    $siteSendlistOpentime[$open['sendlist_id']] = $open['open_time'];
                    $emailOpentime[$open['user_id']] = $open['open_time'];
                    $insertOpenRecordData[] = [$open['user_id'], $open['sendlist_id'], $open['open_time']];
                }
                
                // 执行同步
                $trans = Yii::$app->db->beginTransaction();
                try {
                    $sendListModel->updateOpen($siteId, $siteSendlistOpentime, $byGroupId);   // 同步sendlist
                    $userModel->updateOpen($siteId, $emailOpentime);  // 同步user表
                    $emailOpenRecordModel->insertOpenRecord($siteId, $insertOpenRecordData); // 更新打开详情表的相关数据

                    // 更改处理过的数据状态
                    Yii::$app->mongodb->getCollection('open_report_record_' . $siteName)->update(array('_id' => $redisValues), array('is_sync' => 1));

                    $trans->commit();
                } catch (\Exception $e) {
                    $trans->rollBack();
                    $msg = $e->getMessage();
                    Elog::logCrontab($msg, 'actionSynOpen', 'exception');
                    return $msg;
                }
            }
        } catch (\Exception $e) {
            $msg = $e->getMessage();
            Elog::logCrontab($msg, 'actionSynOpen', 'exception');
            return $msg;
        }
        return "统计打开邮件完成";
    }
    
    /*
     * 手动将mongo中最近两个月的打开数据刷新到mysql表中
     */
    public function actionDealOpenDataFromMongoToMysql()
    {
        ignore_user_abort(true);
        set_time_limit(0);
        
        $method = yii::$app->request->getMethod();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 1000; // 每次处理的数据量
        $startTime = yii::$app->request->$method("startTime") ? yii::$app->request->$method("startTime") : 0; 
        $count = yii::$app->request->$method("count") ? yii::$app->request->$method("count") : 0; 
        
        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            return 'site information does not exist! params: siteId:' . $siteId;
        }
        
        $opensArr = [];
        
        try {
            $mongoQuery = new Query();

            if($count) {
                $opens = $mongoQuery->from('open_report_record_' . $siteName)->where(['is_sync' => 1])->orderBy(['_id' => SORT_DESC])->count();
                var_dump($opens);exit;
            } else {
                $opensArr = $mongoQuery->from('open_report_record_' . $siteName)->where(['is_sync' => 1])->orderBy(['_id' => SORT_DESC])->limit($limit)->all();
            }
        
        } catch(\Exception $e) {
            echo $e->getMessage().'<br/>';
            echo $e->getTraceAsString().'<br/>';
        }
        
        
//        var_dump($opensArr);exit;
        if($opensArr) {
            $_ids = $sendlistIds = $sendlistInfos = $insertArr = [];
            foreach($opensArr as $open) {
                $_ids[] = $open['_id'];
                $sendlistIds[] = $open['sendlist_id'];
            }
            
            if($sendlistIds){
                $sql = "select id,site_user_id,email_type from mail_sendlist_{$siteName} where id in(".join(',', array_unique($sendlistIds)).")";
                
                $sendRes = Yii::$app->db->createCommand($sql)->queryAll();
                
                foreach($sendRes as $send) {
                    $sendlistInfos[$send['id']] = ['site_user_id' => $send['site_user_id'], 'email_type'=> $send['email_type']];
                }
                
            }
            
            foreach($opensArr as $open) {
                $group_id = isset($open['group_id']) ? intval($open['group_id']) : 0;
                $sendlist_id = isset($open['sendlist_id']) ? intval($open['sendlist_id']) : 0;
                $user_id = isset($open['user_id']) ? intval($open['user_id']) : 0;
                $site_user_id = isset($sendlistInfos[$open['sendlist_id']]['site_user_id']) ? intval($sendlistInfos[$open['sendlist_id']]['site_user_id']) : 0;
                $open_teminal = isset($open['open_teminal']) ? intval($open['open_teminal']) : 0;
                $email_type = isset($sendlistInfos[$open['sendlist_id']]['email_type']) ? intval($sendlistInfos[$open['sendlist_id']]['email_type']) : 0;
                $open_time = isset($open['open_time']) ? intval($open['open_time']) : 0;
                $open_count = isset($open['open_count']) ? intval($open['open_count']) : 0;
                $is_sync = isset($open['is_sync']) ? intval($open['is_sync']) : 0;
                
                $insertArr[] = "($group_id,$sendlist_id,$user_id,$site_user_id,$open_teminal,$email_type,$open_time,$open_count,$is_sync)";
            }
            
            if($insertArr) {
                $insertSql =  "insert into email_{$siteName}_open_data"
                        . "(group_id,sendlist_id,user_id,site_user_id,open_teminal,email_type,open_time,open_count,is_sync) values".join(',',$insertArr)
                        . ' ON DUPLICATE KEY UPDATE is_sync=values(is_sync),open_count=values(open_count)';
                
                $trans = Yii::$app->db->beginTransaction();
                try {
                    $result = Yii::$app->db->createCommand($insertSql)->execute();
                    // 更改处理过的数据状态
                    Yii::$app->mongodb->getCollection('open_report_record_' . $siteName)->update(array('_id' => $_ids), array('is_sync' => 2));

                    $trans->commit();
                } catch (\Exception $e) {
                    $trans->rollBack();
                    $msg = $e->getMessage();
                    return $msg;
                }
                
            }
            echo '数据处理中！';
            echo "<script type='text/javascript'>location.href='/marketing/mail-user-deal/deal-open-data-from-mongo-to-mysql?module_name=marketing_email&site_id={$siteId}&limit={$limit}&startTime={$startTime}';</script>";
            exit;
        }
        
       echo '数据处理完成！';
    }
    
    /**
     * 新版同步mongodb的打开记录到真实表中,分网站定时每15分钟执行一次
     * @return string
     */
    public function actionNewSynOpen()
    {
        ignore_user_abort(true);
        set_time_limit(0);
        $method = yii::$app->request->getMethod();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 1000; // 每次处理的数据量
        $byGroupId = yii::$app->request->$method("byGroupId") ? yii::$app->request->$method("byGroupId") : 0; // 是否按照分组来发

        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            $msg = 'site information does not exist! params: siteId:' . $siteId;
            Elog::logCrontab($msg, 'actionSynOpen', 'info');
            return 'site information does not exist! params: siteId:' . $siteId;
        }

        // 对当前请求加锁
//        $redis = new RedisMarketingEmail();
//        
//        $redisLock = new RedisLock($redis, "actionUserOpen-".$siteName, 300);
//            
//        if ($redisLock->translock() === false) {
//            $msg = 'There is a request to reject the request in the process.';
//            Elog::logCrontab($msg, 'actionSynOpen','info');
//            return '有请求在处理中，拒绝该请求！';
//           
//        }

        $emailOpenRecordModel = new EmailOpenRecord();
        $userModel = new User();

        try {
            // 从mongo中获取当前网站的未处理的数据，每次取1000条
//                $opensArr = $query->from('open_report_record_'.$siteName)->where(['is_sync'=>0])->limit($limit)->all();
            while ($opensArr = $this->getOpenRecord($siteName, $byGroupId, $limit)) {
                // 变量初始化
                $redisValues = $siteSendlistOpentime = $emailOpentime = $insertOpenRecordData = $groupTemplateid = [];
                foreach ($opensArr as $open) {

                    $redisValues[] = $open['id'];

                    $emailOpentime[$open['user_id']] = $open['open_time'];
                    $insertOpenRecordData[] = [$open['user_id'], $open['sendlist_id'], $open['open_time']];
                }
                // 执行同步
                $trans = Yii::$app->db->beginTransaction();
                try {
                    
                    $userModel->updateOpen($siteId, $emailOpentime);  // 同步user表
                    $emailOpenRecordModel->insertOpenRecord($siteId, $insertOpenRecordData); // 更新打开详情表的相关数据

                    // 更改处理过的数据状态
                    Yii::$app->db->createCommand("update email_".$siteName."_open_data set is_sync = 1 where id in(".join(',', $redisValues).")")->execute();

                    $trans->commit();
                } catch (\Exception $e) {
                    $trans->rollBack();
                    $msg = $e->getMessage();
                    Elog::logCrontab($msg, 'actionSynOpen', 'exception');
                    return $msg;
                }
            }
        } catch (\Exception $e) {
            $msg = $e->getMessage();
            Elog::logCrontab($msg, 'actionSynOpen', 'exception');
            return $msg;
        }
        return "统计打开邮件完成";
    }
    
    /**
     * 从 email_zaful_open_data 里面去拿还没有同步的打开数据
     * @param string $siteName 站点名称
     * @param null $groupId 分组 ID，不传则随机取一个分组 ID
     * @param int $limit 分页限制
     * @return array
     */
    public function getOpenRecord($siteName, $groupId = 0, $limit = 1000)
    {
        $opensArr = [];
        //如果没有指定分组ID，则随机拿一个分组
        if (0 === $groupId) {
            
            $groupIds = Yii::$app->db->createCommand("select group_id from email_".$siteName."_open_data where is_sync = 0 group by group_id")->queryAll();
            
            if (empty($groupIds)) {
                return $opensArr;
            }
            
            $groupId = $groupIds[array_rand($groupIds)];
        }
        
        $opensArr = Yii::$app->db->createCommand("select * from email_".$siteName."_open_data where is_sync = 0 and group_id = {$groupId['group_id']} limit $limit")->queryAll();
        return $opensArr;
    }
    
    /**
     * 从 MongoDB 里面去拿还没有同步的打开数据
     * @param string $siteName 站点名称
     * @param null $groupId 分组 ID，不传则随机取一个分组 ID
     * @param int $limit 分页限制
     * @return array
     */
    public function getOpenRecordFromMongo($siteName, &$groupId = 0, $limit = 1000)
    {
        $opensArr = [];
        $mongoQuery = new Query();
        //如果没有指定分组ID，则随机拿一个分组
        if (0 === $groupId) {
            $groupIds = $mongoQuery->from('open_report_record_' . $siteName)->where(['is_sync' => 0])->distinct('group_id');
            if (empty($groupIds)) {
                return $opensArr;
            }
            $groupId = $groupIds[array_rand($groupIds)];
        }
        $opensArr = $mongoQuery->from('open_report_record_' . $siteName)->where(['is_sync' => 0, 'group_id' => $groupId])->limit($limit)->all();
        return $opensArr;
    }

    /**
     * 按分组 ID 统计打开数据和设备数据
     * @return string
     * @throws yii\db\Exception
     */
    public function actionSynOpenByGroupId()
    {
        $method = yii::$app->request->getMethod();
        $statModel = new MailGroupEmailTypeStatistics();
        $mailGroupModel = new MailGroup();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $groupIdFromRequest = yii::$app->request->$method("group_id") ? yii::$app->request->$method("group_id") : 0;
        if (!$siteId) {
            $sites = Yii::$app->params['sites']['marketing_email']['web_index'];
            $siteId = array_rand($sites);
        }
        $days = yii::$app->request->$method("days") ? yii::$app->request->$method("days") : 1;
        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            $msg = 'site information does not exist! params: siteId:' . $siteId;
            Elog::logCrontab($msg, 'actionSynOpen', 'info');
            return 'site information does not exist! params: siteId:' . $siteId;
        }
        $mongoQuery = new Query();
        if (!$groupIdFromRequest) {
            //统计最近一段时间有打开记录的分组
            $sitegroupIds = $mongoQuery->from('open_report_record_' . $siteName)->where(['between', 'open_time', strtotime('-' . $days . ' days'), time()])->distinct('group_id');
        } else {
            $sitegroupIds = explode(',', $groupIdFromRequest);
        }

        $siteUniqueGroupIds = array_unique($sitegroupIds);
        $groupInfo = $mailGroupModel->getGroupByIdArr($siteUniqueGroupIds);

        foreach ($groupInfo as $group) {
            $groupTemplateid[$group['group_id']] = $group['template_id'];
        }

        //              $mailGroupModel->updateOpen($siteId, $siteUniqueGroupIds);

        foreach ($siteUniqueGroupIds as $groupId) {
            $mailGroupModel->updateOpenCount($siteId, $groupId);
            $statModel->updateMailOpen($siteId, $groupId);

            // 将打开设备的数据统计到email_group_open_terminal_statictics
            $openTerminalAll = Yii::$app->mongodb->getCollection('open_report_record_' . $siteName)->aggregate([['$match' => ['group_id' => $groupId, 'open_teminal' => ['$ne' => null]]], ['$group' => ['_id' => '$open_teminal', 'total' => ['$sum' => 1]]]]);
            //                        var_dump($openTerminalAll);exit;
            $insertSql = 'insert into email_group_open_terminal_statictics(group_id,open_terminal,site_id,template_id,open,update_time) values';
            $insertArr = [];
            $template_id = isset($groupTemplateid[$groupId]) ? $groupTemplateid[$groupId] : 0;
            foreach ($openTerminalAll as $openTerminal) {

                $insertArr[] = "($groupId,{$openTerminal['_id']},$siteId,$template_id,{$openTerminal['total']}," . \yii::$app->bjTime->time() . ")";
            }
            if ($insertArr) {
                $insertSql .= join(',', $insertArr);
                $insertSql .= ' ON DUPLICATE KEY UPDATE open = values(open) ';
                Yii::$app->db->createCommand($insertSql)->execute();
            }
        }
    }
    
    /**
     * 按分组 ID 统计打开数据和设备数据
     * @return string
     * @throws yii\db\Exception
     */
    public function actionNewSynOpenByGroupId()
    {
        $method = yii::$app->request->getMethod();
        $statModel = new MailGroupEmailTypeStatistics();
        $mailGroupModel = new MailGroup();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $groupIdFromRequest = yii::$app->request->$method("group_id") ? yii::$app->request->$method("group_id") : 0;
        if (!$siteId) {
            $sites = Yii::$app->params['sites']['marketing_email']['web_index'];
            $siteId = array_rand($sites);
        }
        $days = yii::$app->request->$method("days") ? yii::$app->request->$method("days") : 1;
        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            $msg = 'site information does not exist! params: siteId:' . $siteId;
            Elog::logCrontab($msg, 'actionSynOpen', 'info');
            return 'site information does not exist! params: siteId:' . $siteId;
        }
//        $mongoQuery = new Query();
        if (!$groupIdFromRequest) {
            //统计最近一段时间有打开记录的分组
            $startTime = strtotime('-' . $days . ' days');
            $sitegroupIdsData = Yii::$app->db->createCommand("select group_id from email_".$siteName."_open_data where open_time >= $startTime group by group_id")->queryAll();
            $sitegroupIds = [];
            foreach($sitegroupIdsData as $data) {
                $sitegroupIds[] = $data['group_id'];
            }
        } else {
            $sitegroupIds = explode(',', $groupIdFromRequest);
        }
        $siteUniqueGroupIds = [];
        $siteUniqueGroupIds = array_unique($sitegroupIds);
        $groupInfo = $mailGroupModel->getGroupByIdArr($siteUniqueGroupIds);

        foreach ($groupInfo as $group) {
            $groupTemplateid[$group['group_id']] = $group['template_id'];
        }

        //              $mailGroupModel->updateOpen($siteId, $siteUniqueGroupIds);

        foreach ($siteUniqueGroupIds as $groupId) {
            $mailGroupModel->newUpdateOpenCount($siteId, $groupId);
            $statModel->newUpdateMailOpen($siteId, $groupId);
            

            // 将打开设备的数据统计到email_group_open_terminal_statictics
            $sqlGetStat = "select group_id, open_teminal, count(*) as cnt from email_{$siteName}_open_data 
                      where group_id = {$groupId} group by open_teminal";
            $openTerminalAll = yii::$app->db->createCommand($sqlGetStat)->queryAll();

            
            $insertSql = 'insert into email_group_open_terminal_statictics(group_id,open_terminal,site_id,template_id,open,update_time) values';
            $insertArr = [];
            $template_id = isset($groupTemplateid[$groupId]) ? $groupTemplateid[$groupId] : 0;
            foreach ($openTerminalAll as $openTerminal) {

                $insertArr[] = "($groupId,{$openTerminal['open_teminal']},$siteId,$template_id,{$openTerminal['cnt']}," . \yii::$app->bjTime->time() . ")";
            }
            if ($insertArr) {
                $insertSql .= join(',', $insertArr);
                $insertSql .= ' ON DUPLICATE KEY UPDATE open = values(open) ';
                Yii::$app->db->createCommand($insertSql)->execute();
            }
        }
    }

    /**
     * 同步mongodb的打开记录到真实表中,分网站定时每15分钟执行一次
     * @return string
     */
    public function actionSynOpen1()
    {
        ignore_user_abort(true);
        set_time_limit(0);

        $method = yii::$app->request->getMethod();
        $siteId = yii::$app->request->$method("site_id") ? yii::$app->request->$method("site_id") : 0;
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 2000; // 每次处理的数据量
        $curGroupId = yii::$app->request->$method("curGroupId") ? yii::$app->request->$method("curGroupId") : 0; // 当前处理的分组id

        $siteName = Site::getInstance()->getSiteShortNameById($siteId);
        if (!$siteName) {
            $msg = 'site information does not exist! params: siteId:' . $siteId;
            Elog::logCrontab($msg, 'actionSynOpen', 'info');
            return 'site information does not exist! params: siteId:' . $siteId;
        }

        // 对当前请求加锁
//        $redis = new RedisMarketingEmail();
//        
//        $redisLock = new RedisLock($redis, "actionUserOpen-".$siteName, 300);
//            
//        if ($redisLock->translock() === false) {
//            $msg = 'There is a request to reject the request in the process.';
//            Elog::logCrontab($msg, 'actionSynOpen','info');
//            return '有请求在处理中，拒绝该请求！';
//           
//        }

        $sendListModel = new MailSendlist();
        $emailOpenRecordModel = new EmailOpenRecord();
        $userModel = new User();
        $statModel = new MailGroupEmailTypeStatistics();
        $mailGroupModel = new MailGroup();

        // 从mongo中获取当前网站的未处理的数据
        try {
            $query = new Query;
            if (!$curGroupId) {
                $curGroupId = $query->from('open_report_record_' . $siteName)->where(['is_sync' => 0])->timeout(60000)->max1('group_id');
            }
//            $curGroupId = $query->from('open_report_record_'.$siteName)->select(['group_id'=>1,'_id'=>0])->where(['is_sync'=>0])->orderBy('group_id desc')->timeout(120000)->one();
            var_dump($curGroupId);

            if ($curGroupId) {

                // compose the query
                $opensArr = $query->from('open_report_record_' . $siteName)->select([])->where(['group_id' => $curGroupId, 'is_sync' => 0])->limit($limit)->timeout(60000)->all();

                var_dump($opensArr);
                if ($opensArr) {

                    // 变量初始化
                    $redisValues = $siteSendlistOpentime = $siteSendlistIds = $sitegroupIds = $emailOpentime = $insertOpenRecordData = [];
                    foreach ($opensArr as $open) {

                        $redisValues[] = $open['_id'];

                        $siteSendlistOpentime[$open['sendlist_id']] = $open['open_time'];
                        $emailOpentime[$open['user_id']] = $open['open_time'];
                        $sitegroupIds[] = $open['group_id'];
                        $insertOpenRecordData[] = [$open['user_id'], $open['sendlist_id'], $open['open_time']];
                    }

                    // 执行同步
                    $trans = Yii::$app->db->beginTransaction();

                    try {
                        $sendListModel->updateOpen($siteId, $siteSendlistOpentime);   // 同步sendlist
                        $userModel->updateOpen($siteId, $emailOpentime);  // 同步user表
                        $emailOpenRecordModel->insertOpenRecord($siteId, $insertOpenRecordData); // 更新打开详情表的相关数据

                        $siteUniqueGroupIds = array_unique($sitegroupIds);

                        //                $mailGroupModel->updateOpen($siteId, $siteUniqueGroupIds);

                        foreach ($siteUniqueGroupIds as $groupId) {
                            $mailGroupModel->updateOpenCount($siteId, $groupId);
                            $statModel->updateMailOpen($siteId, $groupId);
                        }

                        // 更改处理过的数据状态
                        Yii::$app->mongodb->getCollection('open_report_record_' . $siteName)->update(array('_id' => $redisValues), array('is_sync' => 1));

                        $trans->commit();
                    } catch (\Exception $e) {
                        $trans->rollBack();
                        $msg = $e->getMessage();
                        Elog::logCrontab($msg, 'actionSynOpen', 'exception');
                        return $msg;
                    }

                } else {
                    $curGroupId = $query->from('open_report_record_' . $siteName)->where(['is_sync' => 0, 'group_id' => ['$lt' => $curGroupId]])->timeout(60000)->max1('group_id');
                }


                // 处理完成后
                $host = YII_ENV_PROD ? 'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/';
                echo '当前批次处理完成！';
                echo "<script type='text/javascript'>location.href='" . $host . 'marketing/mail-user-deal/syn-open1?site=marketing_email&site_id=' . $siteId . '&limit=' . $limit . '&curGroupId=' . $curGroupId . "';</script>";
                exit;

            }


        } catch (\Exception $e) {
            $msg = $e->getMessage();
            Elog::logCrontab($msg, 'actionSynOpen', 'exception');
            return $msg;
        }
        return "统计打开邮件完成";

    }

    /**
     * 更新发送数量
     */
    public function actionSynSendStat()
    {
        ignore_user_abort(true);
        set_time_limit(0);

        $groupModel = new MailGroup();
        $statModel = new MailGroupEmailTypeStatistics();

        $method = yii::$app->request->getMethod();
        $groupId = yii::$app->request->$method("groupId");
        $all = yii::$app->request->$method("all");

        $groupId = intval($groupId);
        if ($groupId) {
            $groupArr[] = $groupModel->getGroupById($groupId);
        } else if ($all) {
            $groupIds = [];
            $groupIdsArr = $statModel->getAllGroup();
            foreach ($groupIdsArr as $groupIdInfo) {
                $groupIds[] = $groupIdInfo['gid'];
            }
            $groupArr = $groupModel->getGroupByIdArr($groupIds);
        } else {
            // 获取最后发送时间为两个小时以内的发送分组数
            $lastTime = time() - 3600 * 2;
            $groupArr1 = $groupModel->getGroupByLastSend($lastTime);


            $groupIds = $groupArr = $key_array = [];
            $groupIdsArr = $statModel->getAllGroup();
            foreach ($groupIdsArr as $groupIdInfo) {
                $groupIds[] = $groupIdInfo['gid'];
            }
            $groupArr2 = $groupModel->getGroupByIdArr($groupIds);
            $groupAll = array_merge($groupArr1, $groupArr2);

            foreach ($groupAll as $group) {
                if (!in_array($group['group_id'], $key_array)) {
                    $key_array[] = $group['group_id'];
                    $groupArr[] = $group;
                }
            }
        }
//        var_dump($groupArr);exit;
        foreach ($groupArr as $group) {
            if(isset($group['site_id'], $group['group_id'])) {
                $statModel->updateSent($group['site_id'], $group['group_id']);
                //统计本地数据
                $statModel->updateLocalStatistics($group['site_id'], $group['group_id']);
            }
        }
        echo "统计发送邮件完成";
    }
    
    /**
     * 更新发送数量
     */
    public function actionNewSynSendStat()
    {
        ignore_user_abort(true);
        set_time_limit(0);

        $groupModel = new MailGroup();
        $statModel = new MailGroupEmailTypeStatistics();

        $method = yii::$app->request->getMethod();
        $groupId = yii::$app->request->$method("groupId");
        $all = yii::$app->request->$method("all");

        $groupId = intval($groupId);
        if ($groupId) {
            $groupArr[] = $groupModel->getGroupById($groupId);
        } else if ($all) {
            $groupIds = [];
            $groupIdsArr = $statModel->getAllGroup();
            foreach ($groupIdsArr as $groupIdInfo) {
                $groupIds[] = $groupIdInfo['gid'];
            }
            $groupArr = $groupModel->getGroupByIdArr($groupIds);
        } else {
            // 获取最后发送时间为两个小时以内的发送分组数
            $lastTime = time() - 3600 * 2;
            $groupArr1 = $groupModel->getGroupByLastSend($lastTime);


            $groupIds = $groupArr = $key_array = [];
            $groupIdsArr = $statModel->getAllGroup();
            foreach ($groupIdsArr as $groupIdInfo) {
                $groupIds[] = $groupIdInfo['gid'];
            }
            $groupArr2 = $groupModel->getGroupByIdArr($groupIds);
            $groupAll = array_merge($groupArr1, $groupArr2);

            foreach ($groupAll as $group) {
                if (!in_array($group['group_id'], $key_array)) {
                    $key_array[] = $group['group_id'];
                    $groupArr[] = $group;
                }
            }
        }
//        var_dump($groupArr);exit;
        foreach ($groupArr as $group) {
            if(isset($group['site_id'], $group['group_id'])) {
                $statModel->newUpdateSent($group['site_id'], $group['group_id']);
                //统计本地数据
                $statModel->newUpdateLocalStatistics($group['site_id'], $group['group_id']);
            }
        }
        echo "统计发送邮件完成";
    }

    /**
     * 同步旧edm退订数据
     */
    public function actionSynOldEdmUnsubscribe()
    {
        // 忽略用户中断并且不限请求时间
        ignore_user_abort(true);
        set_time_limit(0);

        $method = yii::$app->request->getMethod();
        $siteName = yii::$app->request->$method("site_name");                // 站点名称：如 gamiss.com
        $synHour = yii::$app->request->$method("syn_hour");         // 同步退订的小时数
        $unSubscribeNum = 0;
        $userModel = new User();
        $emailObj = new EmailMarket();
        $siteId = Site::getInstance()->getSiteId($siteName);
        if (empty($siteId)) {
            exit("站点[{$siteName}]不存在");
        }

        if (empty($synHour)) {      // 默认同步最近24小时
            $synHour = 24;
        }

        // 当前小时的unix时间。如果现在是16:23:07，那么curHourTime取的是16:00:00的unix时间
        $curHourTime = yii::$app->bjTime->strtotime(yii::$app->bjTime->date("Y-m-d H:00:00"));

        $startTime = $curHourTime - $synHour * 3600;
        $endTime = $curHourTime;
        $page = 1;
        $pageSize = 3000;
        $keysCode = 'c346eb79b0b21b26a9fMFj8M7xPMoO0O';
        $apiUrl = 'http://mx.egocdn.com/syn/syn_unsubscribe_to_web.php';

        do {
            $postArr = [
                "keys_code" => $keysCode,
                "site_name" => $siteName,
                "page" => $page,
                "pagesize" => $pageSize,
                "start_time" => $startTime,
                "end_time" => $endTime
            ];

            // 向旧edm系统发送同步退订请求
            $item['url'] = $apiUrl;
            $item['options'][CURLOPT_POST] = 1;
            $item['options'][CURLOPT_POSTFIELDS] = $postArr;
            $item['options'][CURLOPT_RETURNTRANSFER] = 1;
            $item['options'][CURLOPT_TIMEOUT] = 300;
            $result = yii::$app->curl->execute($item);
            $data = json_decode(urldecode($result), true);
            if ($data['errmsg'] != 'success') {
                exit("同步失败： {$data['errmsg']}");
            }

            // 将退订信息写入到数据库
            foreach ($data['data'] as $email) {
                $subscribeState = 2;    // 退订
                $frequency = 1;
                $source = 4;        // EDM邮件
                $emailEncrypt = $emailObj->encryption($email);
                $userModel->updateSubscribeInfo($siteId, $emailEncrypt, $subscribeState, $frequency, $source);
            }

            $unSubscribeNum += count($data['data']);
            if (count($data['data']) < $pageSize) {     // 返回的退订数小于pageSize，表示退订数据已取完。
                break;
            }

            $page++;    // 请求下一页的退订数据
        } while (1);
        exit("同步退订用户[{$unSubscribeNum}]");
    }

}
